package com.tuozixuan.onesql.ui;

import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.tuozixuan.onesql.db.Column;
import com.tuozixuan.onesql.service.TableContext;
import com.tuozixuan.onesql.service.TableServiceImpl;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.geometry.Orientation;
import javafx.scene.control.Button;
import javafx.scene.control.SplitPane;
import javafx.scene.control.Tab;
import javafx.scene.control.TabPane;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextArea;
import javafx.scene.control.ToolBar;
import javafx.scene.control.cell.MapValueFactory;
import javafx.scene.layout.VBox;

public class SqlQueryBox {
	
	private VBox vBox = new VBox();
	
	private TextArea sqlTextArea;
	
	private TableView tableView;

	public SqlQueryBox(TabPane tabPane, TableContext tableContext) {
		
	    ToolBar sqlToolBar = this.buildToolBar(tableContext);
        sqlTextArea = new TextArea();
        
        tableView = new TableView();
        tableView.setEditable(true);
        tableView.autosize();
        
        SplitPane splitPane = new SplitPane();
        splitPane.setOrientation(Orientation.VERTICAL);
        splitPane.getItems().addAll(sqlTextArea, tableView);
        
        vBox.getChildren().addAll(sqlToolBar, splitPane);
        
        Tab tab1 = new Tab(tableContext.getTableName() + "@" + tableContext.getDatabaseName());
        tab1.setContent(vBox);
        tabPane.getTabs().add(tab1);
	}
	
    private void openTable(TableContext tableContext, String sql) throws SQLException {
        
        ObservableList<Map<String,Object>> tableData = this.buildTableData(sql);
        List<Column> columnList = tableContext.getColumnList();
        
        tableView.getColumns().clear();
        for (Column column : columnList) {
            TableColumn tableColumn = new TableColumn(column.getName());
            tableColumn.setCellValueFactory(new MapValueFactory(column.getName()));
            tableView.getColumns().add(tableColumn);
        }
        tableView.setItems(tableData);
    }
    
    private ObservableList<Map<String,Object>> buildTableData(String sql) {
        List<Map<String,Object>> dataList = new TableServiceImpl().getDataBySql(sql);
        List<Map<String,Object>> targetDataList = Lists.newArrayList();
        for (Map<String,Object> dataMap : dataList) {
            Map<String,Object> targetDataMap = Maps.newHashMap();
            for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
                Object targetValue = entry.getValue();
                if (entry.getValue() instanceof Date) {
                    DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    targetValue = sdf.format((Date)entry.getValue());
                } else if (entry.getValue() instanceof LocalDateTime) {
                    DateTimeFormatter sdf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                    targetValue = sdf.format((LocalDateTime)entry.getValue());
                } else if (entry.getValue() instanceof LocalDate) {
                    DateTimeFormatter sdf = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                    targetValue = sdf.format((LocalDate)entry.getValue());
                }
                
                targetDataMap.put(entry.getKey(), targetValue);
            }
            targetDataList.add(targetDataMap);
        }
        ObservableList<Map<String,Object>> tableData = FXCollections.observableArrayList(targetDataList);
        return tableData;
    }
    
    private ToolBar buildToolBar(TableContext tableContext) {
        Button runBtn = new Button("运行");
        Button stopBtn = new Button("停止");
        Button saveBtn = new Button("保存");
        Button exportBtn = new Button("导出");
        
        ToolBar sqlToolBar = new ToolBar();
        sqlToolBar.getItems().addAll(runBtn, stopBtn, saveBtn, exportBtn);
        
        runBtn.setOnMouseClicked(mouseEvent -> {
            String sql = sqlTextArea.getText();
            try {
                this.openTable(tableContext, sql);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            
        });
        
        return sqlToolBar;
    }
}
